﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.Sql;
using System.IO;

//using CommonClasses;



namespace CM_Classes
{
      
    public class UserGroup
    {
        //User User = new User();

        string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";

        private bool usrGrpDisabled;

        public bool UsrGrpDisabled
        {
            get { return usrGrpDisabled; }
            set { usrGrpDisabled = value; }
        }

        private int empID;

        public int EmpID
        {
            get { return empID; }
            set { empID = value; }
        }

         private int groupID;

        public int GroupID
        {
            get { return groupID; }
            set { groupID = value; }
        }

        private String groupName;
        public String GroupName
        {
            get { return groupName; }
            set { groupName = value; }
        }

        private String userName;
        public String UserName
        {
            get { return userName; }
            set { userName = value; }
        }


      //  public List<User> User { get; set; }
        public List<Group> Groups { get; set; }
        public List<UserGroup> UserGroups { get; set; }

        //*************** end of properties

        public string AssignUserToGroup(User user, Group group) //assign a user to a group, records stored in database
        {
            User User = new User();
            Group Group = new Group();
            string result = "";

            GetIDfromName(User.LastName);

            SqlConnection connection = new SqlConnection(connectionString);
            {
                try //inserts row into UserGroup table
                {

                    using (SqlCommand insertCommand = new SqlCommand(
                    "INSERT INTO dbo.UserGroup VALUES(@EmpID, @GroupID,  @UserGrpDisabled)", connection))
                    {
                        insertCommand.Parameters.AddWithValue("@EmpID", empID);
                        insertCommand.Parameters.AddWithValue("@GroupID", groupID);
                        insertCommand.Parameters.AddWithValue("@UserGrpDisabled", usrGrpDisabled);

                        insertCommand.Connection = connection;
                        connection.Open();
                        insertCommand.ExecuteNonQuery();
                        result = " Success ";
                    }
                }//end try
                catch (System.Data.SqlClient.SqlException sqlException)
                {
                    string error = string.Empty;
                    //Log log = new Log();
               //     log.LogTransaction(EmpID, " ", ex.Message, error);
                    result = sqlException.Message;
                }
                catch (Exception e)
                {
                   // string error = string.Empty;
                    //Log log = new Log();
                 //   log.LogTransaction(EmpID, " ", ex.Message, error);
                    result = "There was an error assigning the employee to the group. " + e.Message;
                }

            }

            return result;
        }

    

        public string DeleteUserFromGroup(User user, Group group)
        {
            string result = string.Empty;
            User User = new User();
            GetIDfromName(User.LastName);


            SqlConnection connection = new SqlConnection(connectionString);

            try
            {
                using (SqlCommand deleteCommand = new SqlCommand(
                "DELETE FROM dbo.UserGroup WHERE  EmpID='" + EmpID  + "'AND GroupID ='" + GroupID + "'", connection))
                {

                    deleteCommand.Connection = connection;
                    connection.Open();
                    deleteCommand.ExecuteNonQuery();
                    result = "This employee has been removed from the group." ;
                }
            }//end try
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                string error = string.Empty;
                //Log log = new Log();
                //log.LogTransaction(EmpID, " ", ex.Message, error);
                result = sqlException.Message;
            }
            return result;
        }
        
       
        public List<UserGroup> GetUserGroups()
        {
            
            List<UserGroup> userGroupList = new List<UserGroup>();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
               // string queryString = "Select *  from  [ConfigurationManager].[dbo].[UserGroup]";

                string queryString = String.Format(@"SELECT u.UserName as [UserName], g.groupName as [Group Name], ug.userGrpDisabled
                        FROM [dbo].[UserGroup] as ug, [dbo].[Group] as g , dbo.[User] as u
                         WHERE ug.GroupID = g.GroupID");
                try
                {
                    connection.Open();

                    SqlCommand command = new SqlCommand(queryString, connection);

                    SqlDataReader reader = command.ExecuteReader();

                    while (reader.Read())
                    {

                        UserGroup aUserGroup = new UserGroup();

                        //aUserGroup.empID = reader.GetInt32(0);
                        //aUserGroup.groupID = reader.GetInt32(1);
                        aUserGroup.userName = reader.GetString(0);
                        aUserGroup.groupName = reader.GetString(1);
                        aUserGroup.usrGrpDisabled = reader.GetBoolean(2);

                        userGroupList.Add(aUserGroup);
                    }

                }

                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

                return userGroupList;
            }
        }

        public User SearchGroupForUser(User user)
        {
            User User = new User();
            GetIDfromName(User.LastName);

            try
            {
                SqlConnection connection = new SqlConnection(connectionString);
                SqlCommand SelectCommand = new SqlCommand("Select *  from dbo.UserGroup WHERE  EmpID='" + EmpID, connection);
                connection.Open();

                return user;
            }//end try
            catch (System.Data.SqlClient.SqlException ex)
            {
                string error = string.Empty;
                Console.WriteLine(ex.Message);
                //Log log = new Log();
                //log.LogTransaction(EmpID, " ", ex.Message, error);
                throw;
            }
        
        
        }

        public User GetIDfromName(string LastName)
        {
            //string imgLocation = "";
            string results = "";
            User user = new User();
            string findUser = string.Empty;
            string connectionString = @"Data Source=t-sql;Initial Catalog=ConfigurationManager;Integrated Security=True";
            List<User> User = new List<User>();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    conn.Open();
                    var addUserSP = "FindUserByLastName";
                    using (SqlCommand findUserCommand = new SqlCommand(addUserSP, conn))
                    {
                        findUserCommand.CommandType = CommandType.StoredProcedure;
                        findUserCommand.Parameters.AddWithValue("@LastName", this.LastName);

                        SqlDataReader reader = findUserCommand.ExecuteReader();
                        reader.Read();
                        if (reader.HasRows)
                        {
                            user.FirstName = reader[0].ToString();
                            user.LastName = reader[1].ToString();
                            user.UserName = reader[2].ToString();
                            user.Password = reader[3].ToString();
                            //byte[] PhotoID = (byte[])(reader[4]);
                            user.UserDisabled = Convert.ToBoolean(reader[5].ToString());

                            //if (img == null)
                            //    imgbxPhotoID.Image = null;
                            //else
                            //{
                            //    Memorystream ms = new Memorystream(img);
                            //    imgbxPhotoID.Image = Image.FromStream(ms);
                            //}

                        }
                        else
                        {
                            results = "Employee not found.";
                        }

                    }
                }
                catch (SqlException sqlEx)
                {
                    results = sqlEx.Message;
                    string error = string.Empty;
                   // Log log = new Log();
                   // log.LogTransaction(EmpID, " ", sqlEx.Message, error);
                    throw;
                }
                catch (Exception ex)
                {
                    string error = string.Empty;
                    //Log log = new Log();
                    //log.LogTransaction(EmpID, " ", ex.Message, error);
                    results = ex.Message;
                    throw;
                }
            }

            return user;
        }


        public object LastName { get; set; }
    }
}
